﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;
using MySqlConnector;

namespace CSharp.Infrastructure
{
    public class MySqlBigDALHelper
    {
        private string connectionString = string.Empty;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connectionString">
        /// 批量操作需要包含 charset=utf8;AllowLoadLocalInfile=true
        /// </param>
        public MySqlBigDALHelper(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public event Action<string> Notify = null;

        public bool ListInsert<T>(List<T> list, string tb_name = "") where T : class, new()
        {
            tb_name = ReadTbName<T>(tb_name);

            Dictionary<string, PropertyInfo> dic = new Dictionary<string, PropertyInfo>();
            DataTable dt = new DataTable(tb_name);
            var ps = typeof(T).GetProperties();
            foreach (var p in ps)
            {
                var attributes = (DescriptionAttribute[])p.GetCustomAttributes(typeof(DescriptionAttribute), false);
                if (attributes.Length == 0)
                {
                    continue;
                }

                string desc = attributes[0].Description;
                dt.Columns.Add(p.Name);
                dic.Add(p.Name, p);
            }

            foreach (var item in list)
            {
                var dr = dt.NewRow();
                foreach (var key in dic)
                {
                    if (key.Value.PropertyType == typeof(double))
                    {
                        try
                        {
                            dr[key.Key] = Convert.ToDouble(key.Value.GetValue(item, null));
                        }
                        catch
                        {
                            dr[key.Key] = 0.0;
                        }
                    }
                    else if (key.Value.PropertyType == typeof(int))
                    {
                        try
                        {
                            dr[key.Key] = Convert.ToInt32(key.Value.GetValue(item, null));
                        }
                        catch
                        {
                            dr[key.Key] = 0;
                        }
                    }
                    else
                    {
                        dr[key.Key] = key.Value.GetValue(item, null) as string;
                    }
                }

                dt.Rows.Add(dr);
            }

            ShowMsg("over create table");
            var ret = DateSetInsert(dt);
            ShowMsg("over insert");

            return ret;
        }

        private string ReadTbName<T>(string tb_name) where T : class, new()
        {
            if (string.IsNullOrEmpty(tb_name))
            {
                System.ComponentModel.DataAnnotations.Schema.TableAttribute info = (System.ComponentModel.DataAnnotations.Schema.TableAttribute)typeof(T).GetCustomAttributes().First(a => a.GetType() == typeof(System.ComponentModel.DataAnnotations.Schema.TableAttribute));
                if (info == null)
                {
                    throw new Exception("not [Table(\"\")] in class T");
                }

                tb_name = info.Name;
            }

            return tb_name;
        }

        public bool DateSetInsert(DataTable dt)
        {
            try
            {
                using (var conn = new MySqlConnector.MySqlConnection(connectionString))
                {
                    MySqlBulkCopy sqlRevdBulkCopy = new MySqlBulkCopy(conn);           // 引用SqlBulkCopy  

                    sqlRevdBulkCopy.DestinationTableName = dt.TableName;                // 数据库中对应的表名  
                    sqlRevdBulkCopy.NotifyAfter = dt.Rows.Count;                    // 有几行数据  
                    sqlRevdBulkCopy.WriteToServer(dt);

                    return true;
                }
            }
            catch (Exception ex)
            {
                ShowMsg("数据入库异常:" + ex.ToString());

                return false;
            }

        }

        public List<T> QueryAllTable<T>(string tb_name = "") where T : class, new()
        {
            tb_name = ReadTbName<T>(tb_name);
            ShowMsg("start QueryAllTable " + tb_name);

            List<T> result = new List<T>();
            int size = 10000;
            using (var conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                conn.Open();
                for (int i = 0; i < 10000000; i += size)
                {
                    string select_sql = $"select * from {tb_name} limit {i}, {size}";
                    var temp_list = conn.QueryDataList<T>(select_sql);
                    result.AddRange(temp_list);
                    if (temp_list.Count() < size)
                    {
                        break;
                    }
                }
            }

            ShowMsg("end QueryAllTable " + tb_name);
            return result;
        }

        public List<T> QueryAllData<T>(string select_sql) where T : class, new()
        {
            ShowMsg("start QueryAllData " + select_sql);
            List<T> result = new List<T>();
            using (var conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
            {
                conn.Open();
                result = conn.QueryDataList<T>(select_sql);
            }

            ShowMsg("end QueryAllData " + select_sql);
            return result;
        }

        private void ShowMsg(string msg)
        {
            if (Notify != null)
            {
                Notify(msg);
            }
        }
    }

    public static class CommonSqlExtend
    {
        public static DataTable QueryDataTable(this MySql.Data.MySqlClient.MySqlConnection conn, string select_sql_new)
        {
            MySql.Data.MySqlClient.MySqlDataAdapter da = new MySql.Data.MySqlClient.MySqlDataAdapter(select_sql_new, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);

            return ds.Tables[0];
        }

        public static List<T> QueryDataList<T>(this MySql.Data.MySqlClient.MySqlConnection conn, string select_sql_new) where T : class, new()
        {
            var ps = typeof(T).GetProperties().ToList();
            Dictionary<string, PropertyInfo> dic = new Dictionary<string, PropertyInfo>();
            using (MySql.Data.MySqlClient.MySqlCommand sqlcm = new MySql.Data.MySqlClient.MySqlCommand(select_sql_new, conn))
            {
                sqlcm.CommandTimeout = 0;
                using (var sqldr = sqlcm.ExecuteReader())
                {
                    for (int i = 0; i < sqldr.FieldCount; i++)
                    {
                        var p = ps.Find(a => a.Name == sqldr.GetName(i));
                        if (p != null)
                        {
                            dic.Add(sqldr.GetName(i), p);
                        }
                    }

                    List<T> ret = new List<T>();
                    while (sqldr.Read())
                    {
                        T t = new T();
                        foreach (var item in dic)
                        {
                            if (item.Value.PropertyType == typeof(string))
                            {
                                object obj = sqldr[item.Key];
                                if (obj == DBNull.Value || obj == null)
                                {
                                    item.Value.SetValue(t, "");
                                }
                                else
                                {
                                    item.Value.SetValue(t, obj as string);
                                }
                            }
                            else if (item.Value.PropertyType == typeof(double))
                            {
                                object obj = sqldr[item.Key];
                                if (obj == DBNull.Value || obj == null)
                                {
                                    item.Value.SetValue(t, 0);
                                }
                                else
                                {
                                    item.Value.SetValue(t, Convert.ToDouble(obj));
                                }
                            }
                            else if (item.Value.PropertyType == typeof(int))
                            {
                                object obj = sqldr[item.Key];
                                if (obj == DBNull.Value || obj == null)
                                {
                                    item.Value.SetValue(t, 0);
                                }
                                else
                                {
                                    item.Value.SetValue(t, Convert.ToInt32(obj));
                                }
                            }
                            else
                            {
                            }
                        }

                        ret.Add(t);
                    }

                    return ret;
                }
            }
        }
    }
}
